package com.note.providers;

import com.note.provider.Ink.Inks;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class InkDatabaseHelper extends SQLiteOpenHelperEx {

	// ------------------------------------------------------------ Constants
	private static final String TAG = "InkDatabaseHelper";

	private static final int DATABASE_VERSION = 1;
	public static final String DATABASE_PATH = "/mnt/sdcard/note/com.note.notepad/ink";
	public static final String DATABASE_NAME = DATABASE_PATH + "/" + "ink2.db";

	public static final String SQL_CREATE_INKS		= "CREATE TABLE " + Tables.INKS+" ("
		+ Inks._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
		+ Inks.BATCH + " INTEGER,"
		+ Inks.PAGE_NUMBER + " INTEGER,"
		+ Inks.CONTENT + " BLOB,"
		+ Inks.IMAGE_BUFFER + " BLOB,"
		+ Inks.TEXT_BUFFER + " TEXT,"
		+ Inks.Thumbnail+" BLOB"
		+ ");";

	// ------------------------------------------------------------ Interface
	public interface Tables {
		public static final String INKS = "inks2";
	}

	public interface Views {
		public static final String INKS = "inks2_view";
	}

	// ------------------------------------------------------------ Constructor
	public InkDatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	// ------------------------------------------------------------ Override methods
	@Override
	public void onCreate(SQLiteDatabase db) {
		try {
			db.execSQL(SQL_CREATE_INKS);

			createView(db);
			createTrigger(db);
		} catch (SQLException e) {
			Log.e(TAG, e.getMessage());
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}

	// ------------------------------------------------------------ Private methods
	/**
	 * ������ͼ��
	 * @param db SQLite��
	 */
	public static void createView(SQLiteDatabase db) throws SQLException {
		db.execSQL("DROP VIEW IF EXISTS " + Views.INKS + ";");
		db.execSQL("CREATE VIEW " + Views.INKS + " AS SELECT a.*"
		        + ", (SELECT COUNT(*) FROM " + Tables.INKS
		        + " b WHERE " + "a." + Inks.BATCH + " = " + "b." + Inks.BATCH + ") AS " + Inks.BATCH_COUNT
		        + " FROM " + Tables.INKS + " a;");
	}

	/**
	 * ������������
	 * @param db SQLite��
	 */
	private void createTrigger(SQLiteDatabase db) {
		db.execSQL("create trigger if not exists trigger_" + Tables.INKS + "_insert_pagenumber"
				+ " after insert on " + Tables.INKS + " begin"
				+ " update " + Tables.INKS + " set page_number = "
				+ " (select (case when max(page_number) is null then 1 else max(page_number)+1 end) from " + Tables.INKS + " where batch = new.batch)"
				+ " where _id =  new._id and page_number is null;"
				+ " update " + Tables.INKS + " set batch = new._id where _id =  new._id and batch is null;"
				+ " end;");

		db.execSQL("create trigger if not exists trigger_" + Tables.INKS + "_delete_pagenumber"
				+ " after delete on " + Tables.INKS + " begin"
				+ " update " + Tables.INKS + " set page_number = page_number - 1 where batch = old.batch and page_number > old.page_number;"
				+ " end;");
	}

}
